﻿using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Threading.Tasks;
using Devonline.Core;
using OfficeOpenXml;
using OfficeOpenXml.Table;

namespace Devonline.Utils
{
    public static class ExcelUtility
    {
        /// <summary>
        /// 获取 ExcelPackage 对象的引用, 此时并未释放对象资源
        /// </summary>
        /// <returns></returns>
        public static ExcelPackage GetExcelPackage()
        {
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            return new ExcelPackage();
        }
        /// <summary>
        /// 从文件获取 ExcelPackage 对象的引用, 此时并非释放对象资源
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public static ExcelPackage GetExcelPackage(string fileName)
        {
            fileName = fileName.GetAttachmentPath();
            if (!File.Exists(fileName))
            {
                throw new Exception("文件不存在!");
            }

            var file = new FileInfo(fileName);
            if (file.Extension != ".xlsx")
            {
                throw new Exception("只能导入 .xlsx 类型的 excel 文件!");
            }

            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            var excelPackage = new ExcelPackage(file);
            if (excelPackage.Workbook == null || excelPackage.Workbook.Worksheets == null || excelPackage.Workbook.Worksheets.Count == AppSettings.UNIT_ZERO)
            {
                throw new Exception("excel 文件读取失败, 不能读取空的 excel 文件!");
            }

            return excelPackage;
        }

        /// <summary>
        /// 读取 excel data 的名为 sheet name or index 页内容到 excel data
        /// 列名部分由 excel data 的 columns 指定
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="excelPackage"></param>
        /// <param name="excelData"></param>
        /// <param name="queryables"></param>
        /// <param name="action"></param>
        /// <returns></returns>
        public static ExcelPackage Import<T>(this ExcelPackage excelPackage, ExcelData<T> excelData, Dictionary<string, Func<T, object, object>> queryables = null, Action<T, IEnumerable<T>> action = null) where T : class, new()
        {
            if (excelPackage == null)
            {
                throw new ArgumentNullException(nameof(excelPackage));
            }

            if (excelData == null)
            {
                throw new ArgumentNullException(nameof(excelData));
            }

            if (excelPackage.Workbook == null || excelPackage.Workbook.Worksheets == null || excelPackage.Workbook.Worksheets.Count == AppSettings.UNIT_ZERO)
            {
                throw new Exception("excel 文件读取失败, 不能读取空的 excel 文件!");
            }

            // read sheet
            using var sheet = excelData.SheetName.IsNullOrEmpty() ? excelPackage.Workbook.Worksheets[excelData.SheetIndex] : excelPackage.Workbook.Worksheets[excelData.SheetName];
            if (sheet == null)
            {
                throw new Exception($"excel 文件读取失败, 无法读取名为 {excelData.SheetName ?? excelData.SheetIndex.ToString(CultureInfo.CurrentCulture)} 的 excel 表格!");
            }

            excelData.FileName ??= excelPackage.File.Name;
            ReadFromExcelSheet(sheet, excelData, queryables, action);

            return excelPackage;
        }

        /// <summary>
        /// 直接将集合内容写入 excel 文件中
        /// </summary>
        /// <typeparam name="T">要导出的数据集合对象类型</typeparam>
        /// <param name="items">要导出的数据集合</param>
        /// <param name="fileName">导出的文件名</param>
        /// <param name="columnNames">选择列</param>
        /// <returns>返回Workbook</returns>
        public static async Task<byte[]> ExportAsync<T>(ExcelData<T> excelData)
        {
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            using var excelPackage = new ExcelPackage().Export(excelData);
            return await excelPackage.GetAsByteArrayAsync().ConfigureAwait(false);
        }
        /// <summary>
        /// 向当前 excel 继续写入一个表, 适用于连续导出多个 excel 表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="excelPackage"></param>
        /// <param name="excelData"></param>
        /// <returns></returns>
        public static ExcelPackage Export<T>(this ExcelPackage excelPackage, ExcelData<T> excelData)
        {
            if (excelPackage == null)
            {
                throw new ArgumentNullException(nameof(excelPackage));
            }

            if (excelData == null)
            {
                throw new ArgumentNullException(nameof(excelData));
            }

            var sheet = excelPackage.Workbook.Worksheets.Add(excelData.SheetName);
            sheet.Cells.Style.ShrinkToFit = true;
            var attributes = GetAttributes(excelData);
            if (excelData.Data != null && attributes.IsNotNullOrEmpty())
            {
                //load data, TODO TBC 尚不能自动导出引用字段的值
                var members = attributes.Select(x => x.Field).ToArray();
                sheet.Cells.LoadFromCollection(excelData.Data, true, TableStyles.None, BindingFlags.Public | BindingFlags.Instance, members);
                var rowCount = excelData.Data.Count() + AppSettings.UNIT_ONE;
                foreach (var attribute in attributes)
                {
                    sheet.Column(attribute.Index).Width = attribute.Size;
                    //set format
                    if (rowCount > AppSettings.UNIT_ONE && attribute.Format.IsNotNullOrEmpty())
                    {
                        sheet.Cells[AppSettings.UNIT_TWO, attribute.Index, rowCount, attribute.Index].Style.Numberformat.Format = attribute.Format;
                    }
                }
            }

            return excelPackage;
        }

        /// <summary>
        /// 获取导出的错误消息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="excelData"></param>
        /// <returns></returns>
        public static string GetErrorMessage<T>(this ExcelData<T> excelData)
        {
            if (excelData == null)
            {
                throw new ArgumentNullException(nameof(excelData));
            }

            var error = string.Empty;
            if (excelData.Messages != null && excelData.Messages.Any())
            {
                error += typeof(T).GetDisplayName() + @"<br \>";
                foreach (var err in excelData.Messages)
                {
                    error += $@"第 {err.Key} 行: {err.Value} <br \>";
                    if (error.Length >= 300)
                    {
                        error += @"<br \> ...";
                        break;
                    }
                }
            }

            return error;
        }

        /// <summary>
        /// read data from excel sheet
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="excelPackage"></param>
        /// <param name="sheetIndex"></param>
        /// <param name="queryables"></param>
        /// <param name="action"></param>
        /// <returns></returns>
        private static void ReadFromExcelSheet<T>(in ExcelWorksheet sheet, ExcelData<T> excelData, Dictionary<string, Func<T, object, object>> queryables = null, Action<T, IEnumerable<T>> action = null) where T : class, new()
        {
            if (sheet == null)
            {
                throw new ArgumentNullException(nameof(sheet));
            }

            excelData.SheetName ??= sheet.Name;
            var attributes = GetAttributes(excelData);
            if (attributes.IsNullOrEmpty())
            {
                throw new Exception($"excel 文件读取失败, 没有配置可以导出的列!");
            }

            if (sheet.Dimension.Columns <= AppSettings.UNIT_ZERO || sheet.Dimension.Rows <= AppSettings.UNIT_ZERO)
            {
                throw new Exception($"excel 文件读取失败, 文件中没有数据!");
            }

            var (columns, errors) = ReadHeader<T>(sheet, attributes);

            // get data, excel row and column index from 1
            var list = new List<T>();
            if (errors.IsNullOrEmpty() && sheet.Dimension.Rows > AppSettings.UNIT_ONE)
            {
                var comparer = new MemberEqualityComparer<T>();
                for (int row = AppSettings.UNIT_TWO; row <= sheet.Dimension.Rows; row++)
                {
                    // read value
                    var t = new T();
                    var messages = new List<string>();
                    for (int col = AppSettings.UNIT_ONE; col <= sheet.Dimension.Columns; col++)
                    {
                        if (!columns.ContainsKey(col - AppSettings.UNIT_ONE))
                        {
                            continue;
                        }

                        var field = columns[col - AppSettings.UNIT_ONE];
                        var attribute = attributes.FirstOrDefault(x => x.Field.Name == field);

                        try
                        {
                            object value;
                            var cell = sheet.Cells[row, col];
                            try
                            {
                                cell.InvokeGenericMethod(nameof(cell.GetValue), null, out value, attribute.FieldType);
                            }
                            catch (Exception)
                            {
                                value = cell.Value;
                            }

                            if (attribute.Required && value == null)
                            {
                                throw new Exception("不能为空!");
                            }

                            // excel cell delegate method call
                            if (queryables.IsNotNullOrEmpty() && queryables.ContainsKey(attribute.Field.Name))
                            {
                                value = queryables.FirstOrDefault(x => x.Key == attribute.Field.Name).Value(t, value);
                            }

                            if (value != null && attribute.FieldType != value.GetType().GetCoreType())
                            {
                                value = GetPropertyValue(attribute, value);
                            }

                            if (attribute.FieldType == typeof(string) && attribute.MaxLength > 0 && value != null && value.ToString().Length > attribute.MaxLength)
                            {
                                throw new Exception($"输入超过最大长度 {attribute.MaxLength}!");
                            }

                            if (attribute.Field.MemberType == MemberTypes.Property)
                            {
                                var memberInfo = attribute.Field as PropertyInfo;
                                if (memberInfo.CanWrite)
                                {
                                    memberInfo.SetValue(t, (value != null && value is IConvertible) ? Convert.ChangeType(value, attribute.FieldType, CultureInfo.CurrentCulture) : value);
                                }
                            }
                            else if (attribute.Field.MemberType == MemberTypes.Field)
                            {
                                var memberInfo = attribute.Field as FieldInfo;
                                memberInfo.SetValue(t, (value != null && value is IConvertible) ? Convert.ChangeType(value, attribute.FieldType, CultureInfo.CurrentCulture) : value);
                            }
                        }
                        catch (Exception ex)
                        {
                            messages.Add($"<strong>{attribute.Name}</strong> {ex.Message}");
                        }
                    }

                    // check repeat
                    if (list.Contains(t, comparer))
                    {
                        messages.Add("与其他行重复!");
                    }

                    // check for whole row
                    action?.Invoke(t, list);

                    list.Add(t);

                    // process error message
                    if (messages.IsNotNullOrEmpty())
                    {
                        errors.Add(row, string.Join(AppSettings.CHAR_SEMICOLON.ToString(), messages));
                    }
                }
            }

            excelData.Messages = errors;
            excelData.Data = list;
        }
        /// <summary>
        /// 获取到字段的特性
        /// </summary>
        /// <typeparam name="T">要导出的数据集合对象类型</typeparam>
        /// <param name="excelData">excel data 数据对象</param>
        /// <returns>ExportExcelAttribute集合</returns>
        private static List<ExcelAttribute> GetAttributes<T>(ExcelData<T> excelData)
        {
            var attributes = new List<ExcelAttribute>();
            var propertyInfos = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance).Where(x => x.HasAttribute<ExcelAttribute>());
            excelData.Columns ??= propertyInfos.Select(x => x.Name).ToArray();
            var index = AppSettings.UNIT_ONE;
            foreach (var column in excelData.Columns)
            {
                var propertyInfo = propertyInfos.FirstOrDefault(x => x.Name == column);
                if (propertyInfo != null)
                {
                    var attribute = propertyInfo.GetExcelAttribute();
                    if (attribute != null)
                    {
                        if (attribute.Index <= AppSettings.UNIT_ZERO)
                        {
                            attribute.Index = index++;
                        }

                        if (attribute.Size <= AppSettings.UNIT_ZERO)
                        {
                            attribute.Size = AppSettings.DEFAULT_EXCEL_STRING_SIZE;
                        }

                        attributes.Add(attribute);
                    }
                }
            }

            return attributes;
        }
        /// <summary>
        /// 读列头, 返回读出来的列头和错误信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sheet"></param>
        /// <param name="attributes"></param>
        /// <returns></returns>
        private static (Dictionary<int, string>, Dictionary<int, string>) ReadHeader<T>(in ExcelWorksheet sheet, in List<ExcelAttribute> attributes)
        {
            var errors = new Dictionary<int, string>();
            var columns = new Dictionary<int, string>();
            for (int index = AppSettings.UNIT_ZERO; index < sheet.Dimension.Columns; index++)
            {
                var header = (sheet.Cells[AppSettings.UNIT_ONE, index + AppSettings.UNIT_ONE].GetValue<string>() ?? string.Empty).Trim();
                var attribute = attributes.FirstOrDefault(x => x.Name == header);
                if (attribute != null)
                {
                    columns.Add(index, attribute.Field.Name);
                }
            }

            var columnIndex = sheet.Dimension.Columns + 1;
            foreach (var attribute in attributes.Where(x => x.Required).ToList())
            {
                if (!columns.Values.Contains(attribute.Field.Name))
                {
                    errors.Add(columnIndex++, $"缺少必须导入的 <strong>{attribute.Name}</strong> 列!");
                }
            }

            return (columns, errors);
        }
        /// <summary>
        /// 获取单元格内容
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="property"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        private static object GetPropertyValue(ExcelAttribute attribute, object value)
        {
            if (value == null)
            {
                return value;
            }

            var strValue = value.ToString().Trim();
            var result = (value is string) ? strValue : value;
            //property = property.FirstCharToUpper();
            switch (Type.GetTypeCode(attribute.FieldType))
            {
                case TypeCode.Boolean:
                    if (!(value is bool))
                    {
                        result = (strValue == "是" || strValue == "否") ? (strValue == "是") : throw new Exception("只能填\"是\"或\"否\"!");
                    }
                    break;
                case TypeCode.DateTime:
                    if (value is double doubleValue)
                    {
                        result = DateTime.FromOADate(doubleValue);
                    }
                    break;
                default:
                    if (attribute.FieldType.IsEnum)
                    {
                        result = attribute.FieldType.GetField(strValue).GetValue(null);
                        if (result == null)
                        {
                            throw new Exception("格式不正确!");
                        }
                    }

                    break;
            }

            return result;
        }
    }
}